﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class DAO_SinhVien
    {
        public static List<DTO_SinhVien> SelectSinhVienTheoLop(string malop)
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {
                string s = "Proc_SelectDMSinhVienTheoLop";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaLop", SqlDbType.NVarChar).Value = malop;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien sinhvien = new DTO_SinhVien();
                    sinhvien.MaSV = dt.Rows[i]["MaSV"].ToString();
                    sinhvien.TenSV = dt.Rows[i]["TenSV"].ToString();
                    sinhvien.GioiTinh = bool.Parse(dt.Rows[i]["GioiTinh"].ToString());
                    sinhvien.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    sinhvien.MaLop = dt.Rows[i]["MaLop"].ToString();
                    sinhvien.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    sinhvien.Email = dt.Rows[i]["Email"].ToString();
                    sinhvien.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(sinhvien);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception ex)
            {
                return listSinhVien;
            }
        }
        public static List<DTO_SinhVien> SelectSinhVienTheoKhoa(string makhoa)
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {
                string s = "Pr_DSSVtheokhoa";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaKhoa", SqlDbType.NVarChar).Value = makhoa;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien sinhvien = new DTO_SinhVien();
                    sinhvien.MaSV = dt.Rows[i]["MaSV"].ToString();
                    sinhvien.TenSV = dt.Rows[i]["TenSV"].ToString();
                    sinhvien.GioiTinh = bool.Parse(dt.Rows[i]["GioiTinh"].ToString());
                    sinhvien.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    sinhvien.MaLop = dt.Rows[i]["MaLop"].ToString();
                    sinhvien.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    sinhvien.Email = dt.Rows[i]["Email"].ToString();
                    sinhvien.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(sinhvien);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception ex)
            {
                return listSinhVien;
            }
        }
        public static List<DTO_SinhVien> SelectSinhVienTheoMaSV(string masv)
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {
                string s = "Proc_SelectDMSinhVienTheoMaSV";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien sinhvien = new DTO_SinhVien();
                    sinhvien.MaSV = dt.Rows[i]["MaSV"].ToString();
                    sinhvien.TenSV = dt.Rows[i]["TenSV"].ToString();
                    sinhvien.GioiTinh = bool.Parse(dt.Rows[i]["GioiTinh"].ToString());
                    sinhvien.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    sinhvien.MaLop = dt.Rows[i]["MaLop"].ToString();
                    sinhvien.MaKH = dt.Rows[i]["MaKH"].ToString();
                    sinhvien.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    sinhvien.Email = dt.Rows[i]["Email"].ToString();
                    sinhvien.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(sinhvien);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception ex)
            {
                return listSinhVien;
            }
        }
        // delete theo lop
        public static bool deleteSinhVientheoMaLop(string malop)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_deleteSinhVientheoMaLop";
                SqlCommand cmd = new SqlCommand(s, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@MaLop", malop);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public static List<DTO_SinhVien> SelectSinhVienTheoKhoaHoc(string maKhoaHoc)
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {
                string s = "proc_selectDSSinhvien_TheoKhoaHoc";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaKhoaHoc", SqlDbType.NVarChar).Value = maKhoaHoc;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien sinhvien = new DTO_SinhVien();
                    sinhvien.MaSV = dt.Rows[i]["MaSV"].ToString();
                    sinhvien.TenSV = dt.Rows[i]["TenSV"].ToString();
                    sinhvien.GioiTinh = bool.Parse(dt.Rows[i]["GioiTinh"].ToString());
                    sinhvien.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    sinhvien.MaLop = dt.Rows[i]["MaLop"].ToString();
                    sinhvien.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    sinhvien.Email = dt.Rows[i]["Email"].ToString();
                    sinhvien.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(sinhvien);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception ex)
            {
                return listSinhVien;
            }
        }
        public static List<DTO_SinhVien> SelectSinhVien()
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {
                string s = "Proc_SelectSinhVien";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien SV = new DTO_SinhVien();
                    SV.MaSV = dt.Rows[i]["MaSV"].ToString();
                    SV.TenSV = dt.Rows[i]["TenSV"].ToString();
                    if ((bool)dt.Rows[i]["GioiTinh"] == false)
                        SV.GioiTinh = false;
                    else
                        SV.GioiTinh = true;
                    SV.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    SV.MaLop = dt.Rows[i]["MaLop"].ToString();
                    SV.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    SV.Email = dt.Rows[i]["Email"].ToString();
                    SV.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(SV);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception ex)
            {
                return listSinhVien;
            }
        }
        public static bool deleteSinhVien(string malop)
        {
            // lay danh sach lop theo ma lop
            List<DTO_SinhVien> listSV = new List<DTO_SinhVien>();
            try
            {
                string s = "Proc_SelectDMSinhVienTheoLop";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                cmd.Parameters.Add("@MaLop", SqlDbType.NVarChar).Value = malop;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable sv = new DataTable();
                da.Fill(sv);
                for (int i = 0; i < sv.Rows.Count; i++)
                {
                    DTO_SinhVien sinhvien = new DTO_SinhVien();
                    sinhvien.MaSV = sv.Rows[i]["MaSV"].ToString();
                    listSV.Add(sinhvien);
                }
            }
            catch (Exception e)
            {
                return false;
            }

            for (int i = 0; i < listSV.Count; i++)
            {
                DAO_TaiKhoan.deleteTaiKhoanTheoMaSV(listSV[i].MaSV);
                DAO_PhanCongDT.deletePhanCongDTTheoMaSV(listSV[i].MaSV);
            }


            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_deleteLoptheoMaLop";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@Malop", malop);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public static bool deleteSinhVientheoSV(string masv)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_deleteSinhvien";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaSV", masv);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public static bool insertSinhVien(DTO_SinhVien SinhVien)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_InsertSinhVien";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaSV", SinhVien.MaSV);
                cmd1.Parameters.AddWithValue("@TenSV", SinhVien.TenSV);
                cmd1.Parameters.AddWithValue("@GioiTinh", SinhVien.GioiTinh);
                string ngay = SinhVien.NgaySinh.ToString("yyyy/MM/dd");
                cmd1.Parameters.AddWithValue("@NgaySinh", ngay);
                cmd1.Parameters.AddWithValue("@MaLop", SinhVien.MaLop);
                cmd1.Parameters.AddWithValue("@DiaChi", SinhVien.DiaChi);
                cmd1.Parameters.AddWithValue("@Email", SinhVien.Email);
                cmd1.Parameters.AddWithValue("@SDT", SinhVien.SDT);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                DTO_TaiKhoan tk = new DTO_TaiKhoan();
                tk.TenTK = SinhVien.MaSV;
                tk.MatKhau = SinhVien.NgaySinh.ToString("MMddyyyy");
                tk.ID_Nhom = 3;
                DAO_TaiKhoan.InsertDangNhap_VaoTaiKhoan(tk);

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool updateSinhVien(DTO_SinhVien SinhVien, string MaSV_cu)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Proc_UpdateSinhVien";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@MaSV", SinhVien.MaSV);
                cmd1.Parameters.AddWithValue("@TenSV", SinhVien.TenSV);
                cmd1.Parameters.AddWithValue("@GioiTinh", SinhVien.GioiTinh);
                cmd1.Parameters.AddWithValue("@NgaySinh", SinhVien.NgaySinh);
                cmd1.Parameters.AddWithValue("@MaLop", SinhVien.MaLop);
                cmd1.Parameters.AddWithValue("@DiaChi", SinhVien.DiaChi);
                cmd1.Parameters.AddWithValue("@Email", SinhVien.Email);
                cmd1.Parameters.AddWithValue("@SDT", SinhVien.SDT);
                cmd1.Parameters.AddWithValue("@MaSV_Cu", MaSV_cu);
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        public static List<DTO_SinhVien> SelectSinhVien_Dau()
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {
                string s = "Pr_SVDauDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien SV = new DTO_SinhVien();
                    SV.MaSV = dt.Rows[i]["MaSV"].ToString();
                    SV.TenSV = dt.Rows[i]["TenSV"].ToString();
                    if ((bool)dt.Rows[i]["GioiTinh"] == false)
                        SV.GioiTinh = false;
                    else
                        SV.GioiTinh = true;
                    SV.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    SV.MaLop = dt.Rows[i]["MaLop"].ToString();
                    SV.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    SV.Email = dt.Rows[i]["Email"].ToString();
                    SV.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(SV);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception e)
            {
                return listSinhVien;
            }
        }
        public static List<DTO_SinhVien> SelectSinhVien_Rot()
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {
                string s = "Pr_SVRotDT";
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien SV = new DTO_SinhVien();
                    SV.MaSV = dt.Rows[i]["MaSV"].ToString();
                    SV.TenSV = dt.Rows[i]["TenSV"].ToString();
                    if ((bool)dt.Rows[i]["GioiTinh"] == false)
                        SV.GioiTinh = false;
                    else
                        SV.GioiTinh = true;
                    SV.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    SV.MaLop = dt.Rows[i]["MaLop"].ToString();
                    SV.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    SV.Email = dt.Rows[i]["Email"].ToString();
                    SV.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(SV);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception e)
            {
                return listSinhVien;
            }
        }
        public static List<DTO_SinhVien> selectSinhVienfull(int luachon, string k, string malop, string maKhoaHoc)
        {
            List<DTO_SinhVien> listSinhVien = new List<DTO_SinhVien>();
            try
            {

                string s = "";
                if (luachon == 0)
                {
                    s = "Proc_SelectSinhVien";
                }
                else if (luachon == 1)
                {
                    s = "Proc_SelectDMSinhVienTheoLop";
                }
                else if (luachon == 2)
                {
                    s = "proc_selectDSSinhvien_TheoKhoaHoc";
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = ketnoi.Cmb();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = s;
                if (luachon == 1)
                {
                    cmd.Parameters.Add("@MaLop", SqlDbType.NVarChar).Value = malop;
                }
                if (luachon == 2)
                {
                    cmd.Parameters.Add("@MaKhoaHoc", SqlDbType.NVarChar).Value = maKhoaHoc;
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DTO_SinhVien SV = new DTO_SinhVien();
                    SV.MaSV = dt.Rows[i]["MaSV"].ToString();
                    SV.TenSV = dt.Rows[i]["TenSV"].ToString();
                    if ((bool)dt.Rows[i]["GioiTinh"] == false)
                        SV.GioiTinh = false;
                    else
                        SV.GioiTinh = true;
                    SV.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
                    SV.MaLop = dt.Rows[i]["MaLop"].ToString();
                    SV.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                    SV.Email = dt.Rows[i]["Email"].ToString();
                    SV.SDT = dt.Rows[i]["SDT"].ToString();
                    listSinhVien.Add(SV);
                }
                ketnoi.Cmb().Close();
                return listSinhVien;
            }
            catch (Exception e)
            {
                return listSinhVien;
            }
        }

        public static bool Update_TTTaiKhoan(DTO_SinhVien SinhVien, string masv)
        {
            try
            {
                SqlConnection cn = ketnoi.Cmb();
                string s = "Update_TTTaiKhoan";
                SqlCommand cmd1 = new SqlCommand(s, cn);
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.AddWithValue("@DiaChi", SinhVien.DiaChi);
                cmd1.Parameters.AddWithValue("@Email", SinhVien.Email);
                cmd1.Parameters.AddWithValue("@SDT", SinhVien.SDT);
                cmd1.Parameters.Add("@MaSV", SqlDbType.NVarChar).Value = masv;
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
                cmd1.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}

